﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using WeifenLuo.WinFormsUI.Docking;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Oracle;
using System.Data.Common;

namespace GgCarControl
{
    public partial class DISPATCH_SR_STATUS : DockContent
    {

        private class Item
        {
            public string Name;
            public int Value;
            public Item(string name, int value)
            {
                Name = name; Value = value;
            }
            public override string ToString()
            {
                return Name;
            }
        }

        delegate void CheckLabelRereshHandler(DataTable check_cars);
        event CheckLabelRereshHandler CheckRefresh;

        public DISPATCH_SR_STATUS()
        {
            InitializeComponent();
            //initiate the rzfs ComboBox and add items for it
            this.comboBox1.Items.Add(new Item("全部", 99));
            this.comboBox1.Items.Add(new Item("已登记", 0));
            this.comboBox1.Items.Add(new Item("已发指令", 1));
            this.comboBox1.Items.Add(new Item("已发卡", 2));
            this.comboBox1.Items.Add(new Item("已进门", 3));
            this.comboBox1.Items.Add(new Item("已理货", 4));
            this.comboBox1.Items.Add(new Item("已出门", 5));
            this.comboBox1.SelectedIndex = 0;
        }

        public static Database db =
             new OracleDatabase("Data Source=DG;User ID=dgrfid;Password=dgrfid;Unicode=True;Load Balance Timeout=1000");
        DataTable dt;

        private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {

        }

        private void button2_Click(object sender, EventArgs e)
        {
            load_data();
            LocateByCarNo();
        }

        private void LocateByCarNo()
        {
            string strCarNo = this.textBox_carNO.Text.ToUpper().Trim();
            if (!string.IsNullOrEmpty(strCarNo))
            {
                foreach (DataGridViewRow row in dataGridView1.Rows)
                {
                    if (row.Cells["车号"].Value.ToString().Contains(strCarNo))
                    {
                        dataGridView1.CurrentCell = row.Cells[0];
                        row.Selected = true;
                        return;
                    }
                }
            }
        }

        private void dateTimePicker2_ValueChanged(object sender, EventArgs e)
        {
            BD();
        }
        private void BD()
        {//SHIPNAME,cargoname,stackname,CARDADD,SHIPPER_NAME||GETOWNER
           // kjbind(cb_cm, "SHIPNAME");
            kjbind(cb_dl, "CARDADD");
            kjbind(cb_dm, "stackname");
            kjbind(cb_hz, "cargoname");
            kjbind(cb_hzdl, "SHIPPER_NAME||GETOWNER");
        }

        private void kjbind(ComboBox _cb, string _lm)
        {
            _cb.Items.Clear();
            //this.cb_dl.Items.Clear();
            //this.cb_dm.Items.Clear();
            //this.cb_hz.Items.Clear();
            //this.cb_hzdl.Items.Clear();


            string strbegin = dateTimePicker1.Value.ToString("yyyy-MM-dd") + " 00:00:00";
            string strend = dateTimePicker2.Value.ToString("yyyy-MM-dd") + "23:59:59";
            string strwhere2 = " WHERE LOGINTIME>=TO_DATE('" + strbegin + "','yyyy-mm-dd hh24:mi:ss') AND LOGINTIME<=TO_DATE('" + strend + "','yyyy-mm-dd hh24:mi:ss') and JFBZ in (1,2) ";

            string strsq = " SELECT DISTINCT " + _lm + " FROM TRUCK_CARGO_LOGIN " + strwhere2;

            DataTable dt = db.ExecuteDataSet(db.GetSqlStringCommand(strsq.ToString())).Tables[0];

            foreach (DataRow dr in dt.Rows)
            {
                if (!dr.IsNull(0))
                    _cb.Items.Add(dr[0].ToString());
            }



        }
        private void label3_Click(object sender, EventArgs e)
        {

        }

        private void dateTimePicker1_ValueChanged(object sender, EventArgs e)
        {
            BD();
        }

        private void label2_Click(object sender, EventArgs e)
        {

        }

        System.Timers.Timer t = new System.Timers.Timer(60000);//实例化Timer类，设置间隔时间为60秒；
        private void STATUS_SR_Load(object sender, EventArgs e)
        {
            load_data();

            CheckRefresh += new CheckLabelRereshHandler(fresh_check);
            t.Elapsed += new System.Timers.ElapsedEventHandler(theout);//到达时间的时候执行事件；
            t.AutoReset = true;//设置是执行一次（false）还是一直执行(true)；
            t.Enabled = true;//是否执行System.Timers.Timer.Elapsed事件；
            BD();
        }
        void fresh_check(DataTable dt)
        {
            //string new_number = dt.Rows[0][0].ToString();
            this.label_cars.Text = dt.Rows[0][0].ToString();
        }

        public void theout(object source, System.Timers.ElapsedEventArgs e)
        {
            string sql = "select count(*) as login_check from TRUCK_CARGO_LOGIN where TRUCKSTATUS='0' and IOYARDID='1'  and JFBZ in (1,2)";
            DataSet ds = db.ExecuteDataSet(db.GetSqlStringCommand(sql.ToString()));
            if (this.IsHandleCreated)
            {
                this.label_cars.BeginInvoke(CheckRefresh, ds.Tables[0]);
            }
        }

        private void load_data()
        {
            //Begin time value
            string date_time_begin = this.dateTimePicker1.Value.ToString();
            int ind = date_time_begin.IndexOf(" ");
            string date_value_begin = date_time_begin.Substring(0, ind);
            //End time value
            string date_time_end = this.dateTimePicker2.Value.ToString();
            int ind2 = date_time_end.IndexOf(" ");
            string date_value_end = date_time_end.Substring(0, ind2);


            //string strcm = this.cb_cm.Text.ToString();
            string strhm = this.cb_hz.Text.ToString();
            string strdm = this.cb_dm.Text.ToString();
            string strdjdd = this.cb_dl.Text.ToString();
            string strhzdl = this.cb_hzdl.Text.ToString();

            //string sql = "select * from TRUCK_LOGIN where TRUCKSTATUS=0 and ioyardid=1";
            string sql = "select logintime as 登记时间,truckno as  车号,SHIPPER_NAME as 货主代理,BILL_NO as 提单号,cargoname as 货名,"+
                "yard as 地点,ZYJX as 作业机械,DECODE(to_number(TRUCKSTATUS),0,'登记',1,'发指令',2,'放车',3,'进门',4,'理货',5,'出门') as  状态,stackname as 垛名,pieces as 件数," +
                "DRIVERPHONE as 司机电话,STANDARDS as 规格,MATERIAL as 材质,GETOWNER as 收货人,CARDTIME as 放车时间,(CASE WHEN TRUCKSTATUS=2 THEN to_char(ROUND(TO_NUMBER(sysdate - CARDTIME) * 24,1),'fm9999999990.0')||'小时' ELSE '' END) as 已放行时间,DOORINTIME AS 进门时间,"+
                "(CASE WHEN TRUCKSTATUS=3 OR TRUCKSTATUS=4 THEN to_char(ROUND(TO_NUMBER(sysdate - DOORINTIME) * 24,1),'fm9999999990.0')||'小时' ELSE '' END) as 在港时间,"+
                "CARDADD as 登记地点,REMARK as 备注,IOYARDID,TRUCKSTATUS from TRUCK_CARGO_LOGIN" +
                " where TO_NUMBER(TRUCKSTATUS)<>9 and  ioyardid=1 and trunc(logintime)<=to_date('" + date_value_end + "','yyyy-mm-dd') and trunc(logintime)>=to_date('" + date_value_begin + "','yyyy-mm-dd') and JFBZ in (1,2)";

            string loginSL_SQL = "select sum(1) as sl from TRUCK_CARGO_LOGIN where TRUCKSTATUS<>'9' and ioyardid=1 and " +
                "trunc(logintime)<=to_date('" + date_value_end + "','yyyy-mm-dd') and trunc(logintime)>=to_date('" + date_value_begin + "','yyyy-mm-dd') and JFBZ in (1,2)";

            string fcs_SQL = "select sum(1) as sl from TRUCK_CARGO_LOGIN where (truckstatus='2' or truckstatus='3' or truckstatus='4' or truckstatus='5') and ioyardid=1 and " +
                "trunc(COMMANDTIME)<=to_date('" + date_value_end + "','yyyy-mm-dd') and trunc(COMMANDTIME)>=to_date('" + date_value_begin + "','yyyy-mm-dd') and JFBZ in (1,2)";    

            if (strhm.Trim() != "")
            {
                sql += " AND  cargoname LIKE '%" + strhm + "%' ";
                loginSL_SQL += " AND  cargoname LIKE '%" + strhm + "%' ";
                fcs_SQL += " AND  cargoname LIKE '%" + strhm + "%' ";
            }
            if (strdm.Trim() != "")
            {
                sql += " AND  stackname LIKE '%" + strdm + "%' ";
                loginSL_SQL += " AND  stackname LIKE '%" + strdm + "%' ";
                fcs_SQL += " AND  stackname LIKE '%" + strdm + "%' ";
            }
            if (strdjdd.Trim() != "")
            {
                sql += " AND  CARDADD LIKE '%" + strdjdd + "%' ";
                loginSL_SQL += " AND  CARDADD LIKE '%" + strdjdd + "%' ";
                fcs_SQL += " AND  CARDADD LIKE '%" + strdjdd + "%' ";
            }
            if (strhzdl.Trim() != "")
            {
                sql += " AND  (SHIPPER_NAME LIKE '%" + strhzdl + "%' OR GETOWNER LIKE '%" + strhzdl + "%') ";
                loginSL_SQL += " AND  (SHIPPER_NAME LIKE '%" + strhzdl + "%' OR GETOWNER LIKE '%" + strhzdl + "%') ";
                fcs_SQL += " AND  (SHIPPER_NAME LIKE '%" + strhzdl + "%' OR GETOWNER LIKE '%" + strhzdl + "%') ";
            }
            //SHIPPER_NAME||GETOWNER like '%海%'
            sql += " order by TRUCKSTATUS,logintime desc ";
            //loginSL_SQL += "group by cargoname,logintime";
            //fcs_SQL += "group by cargoname,logintime";
            
            dt = db.ExecuteDataSet(db.GetSqlStringCommand(sql.ToString())).Tables[0];

            this.dataGridView1.AutoGenerateColumns = true;
            this.dataGridView1.AllowUserToAddRows = false;
            this.dataGridView1.DataSource = dt;

            //设置某些字段不可见
            this.dataGridView1.Columns["IOYARDID"].Visible = false;
            this.dataGridView1.Columns["TRUCKSTATUS"].Visible = false;

            string sql2 = "select count(*) as login_check from TRUCK_CARGO_LOGIN where TRUCKSTATUS='0' and IOYARDID='1' and JFBZ in (1,2)";
            DataSet ds = db.ExecuteDataSet(db.GetSqlStringCommand(sql2.ToString()));
            this.label_cars.Text = ds.Tables[0].Rows[0][0].ToString();

            //更新登记数和放车数
            //loginSL_SQL = "select sum(case when sl!=0 then 1 else 0 end) from (" + loginSL_SQL + ")";
            //fcs_SQL = "select sum(case when sl!=0 then 1 else 0 end) from (" + fcs_SQL + ")";
            this.lblLoginSL.Text = db.ExecuteDataSet(db.GetSqlStringCommand(loginSL_SQL)).Tables[0].Rows[0][0].ToString();
            this.lblFCS.Text = db.ExecuteDataSet(db.GetSqlStringCommand(fcs_SQL)).Tables[0].Rows[0][0].ToString();
        }

        private void dataGridView1_DataBindingComplete(object sender, DataGridViewBindingCompleteEventArgs e)
        {
            
        }

        private void dataGridView1_DataBindingComplete_1(object sender, DataGridViewBindingCompleteEventArgs e)
        {
            foreach (DataGridViewRow DGVRow in this.dataGridView1.Rows)
            {
                if (DGVRow != null)
                {
                    switch (DGVRow.Cells["TRUCKSTATUS"].Value as string)
                    {
                        case "1":
                            DGVRow.DefaultCellStyle.BackColor = Color.Green;
                            DGVRow.DefaultCellStyle.SelectionForeColor = Color.DarkGreen;
                            break;
                        case "2":
                            DGVRow.DefaultCellStyle.BackColor = Color.Fuchsia;
                            DGVRow.DefaultCellStyle.SelectionForeColor = Color.Fuchsia;
                            break;
                        case "3":
                            DGVRow.DefaultCellStyle.BackColor = Color.Orange;
                            DGVRow.DefaultCellStyle.SelectionForeColor = Color.Orange;
                            break;
                        case "4":
                            DGVRow.DefaultCellStyle.BackColor = Color.LightSkyBlue;
                            DGVRow.DefaultCellStyle.SelectionForeColor = Color.LightSkyBlue;
                            break;
                        case "5":
                            DGVRow.DefaultCellStyle.BackColor = Color.Yellow;
                            DGVRow.DefaultCellStyle.SelectionForeColor = Color.Yellow;
                            break;
                    }
                }
            }
        }


        private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            //Begin time value
            string date_time_begin = this.dateTimePicker1.Value.ToString();
            int ind = date_time_begin.IndexOf(" ");
            string date_value_begin = date_time_begin.Substring(0, ind);
            //End time value
            string date_time_end = this.dateTimePicker2.Value.ToString();
            int ind2 = date_time_end.IndexOf(" ");
            string date_value_end = date_time_end.Substring(0, ind2);

            string str = this.comboBox1.Text.ToString();
            Item selected_item = (Item)this.comboBox1.SelectedItem;
            string selected_value = selected_item.Value.ToString();
            string sql = "";
            if (str == "全部")
            {
                sql = "select logintime as 登记时间,truckno as  车号,SHIPPER_NAME as 货主代理,BILL_NO as 提单号,cargoname as 货名,"+
                    "yard as 地点,ZYJX as 作业机械,DECODE(to_number(TRUCKSTATUS),0,'登记',1,'发指令',2,'放车',3,'进门',4,'理货',5,'出门') as  状态,stackname as 垛名,pieces as 件数," +
                    "DRIVERPHONE as 司机电话,STANDARDS as 规格,MATERIAL as 材质,GETOWNER as 收货人,CARDTIME as 放车时间,(CASE WHEN TRUCKSTATUS=2 THEN to_char(ROUND(TO_NUMBER(sysdate - CARDTIME) * 24,1),'fm9999999990.0')||'小时' ELSE '' END) as 已放行时间,CARDADD as 登记地点,REMARK as 备注,IOYARDID,TRUCKSTATUS from TRUCK_CARGO_LOGIN" +
                " where TRUCKSTATUS<>'9' and ioyardid=1 and trunc(logintime)<=to_date('" + date_value_end + "','yyyy-mm-dd') and trunc(logintime)>=to_date('" + date_value_begin + "','yyyy-mm-dd') and JFBZ in (1,2)  order by logintime desc,TRUCKSTATUS";
            }
            else
            {
                sql = "select logintime as 登记时间,truckno as  车号,SHIPPER_NAME as 货主代理,BILL_NO as 提单号,cargoname as 货名,"+
                    "yard as 地点,ZYJX as 作业机械,DECODE(to_number(TRUCKSTATUS),0,'登记',1,'发指令',2,'放车',3,'进门',4,'理货',5,'出门') as  状态,stackname as 垛名,pieces as 件数," +
                    "DRIVERPHONE as 司机电话,STANDARDS as 规格,MATERIAL as 材质,GETOWNER as 收货人,CARDTIME as 放车时间,(CASE WHEN TRUCKSTATUS=2 THEN to_char(ROUND(TO_NUMBER(sysdate - CARDTIME) * 24,1),'fm9999999990.0')||'小时' ELSE '' END) as 已放行时间,CARDADD as 登记地点,REMARK as 备注,IOYARDID,TRUCKSTATUS from TRUCK_CARGO_LOGIN" +
                " where TRUCKSTATUS<>'9' and ioyardid=1 and TRUCKSTATUS=" + selected_value + " and trunc(logintime)<=to_date('" + date_value_end + "','yyyy-mm-dd') and trunc(logintime)>=to_date('" + date_value_begin + "','yyyy-mm-dd') and JFBZ in (1,2) order by logintime desc,TRUCKSTATUS";
            }
            dt = db.ExecuteDataSet(db.GetSqlStringCommand(sql.ToString())).Tables[0];

            this.dataGridView1.AutoGenerateColumns = true;
            this.dataGridView1.AllowUserToAddRows = false;
            this.dataGridView1.DataSource = dt;

            //设置某些字段不可见
            this.dataGridView1.Columns["IOYARDID"].Visible = false;
            this.dataGridView1.Columns["TRUCKSTATUS"].Visible = false;

        }

        private void dataGridView1_CellMouseDown(object sender, DataGridViewCellMouseEventArgs e)
        {
            //if (e.Button == MouseButtons.Right)//判读是否是鼠标右键
            //{
            //    this.dataGridView1.ClearSelection();
            //    this.dataGridView1.Rows[e.RowIndex].Selected = true;
            //    string status = this.dataGridView1.Rows[e.RowIndex].Cells["TRUCKSTATUS"].Value.ToString();
            //    //MessageBox.Show(status);
            //    if (status == "0")
            //    {
            //        this.SendCommandToolStripMenuItem.Visible = true;
            //        this.CancelCommandToolStripMenuItem.Visible = false;
            //    }
            //    if (status == "1")
            //    {
            //        this.SendCommandToolStripMenuItem.Visible = false;
            //        this.CancelCommandToolStripMenuItem.Visible = true;
            //    }
            //    if (status == "0" || status == "1")
            //    {
            //        contextMenuStrip1.Show(MousePosition.X, MousePosition.Y);//contextMenuStrip1为菜单控件
            //    }
            //}
            if (e.Button == MouseButtons.Right)//判读是否是鼠标右键
            {
                this.dataGridView1.Rows[e.RowIndex].Selected = true;
                this.SendCommandToolStripMenuItem.Visible = true;
                this.CancelCommandToolStripMenuItem.Visible = true;
                contextMenuStrip1.Show(MousePosition.X, MousePosition.Y);//contextMenuStrip1为菜单控件
            }
        }

        private void SendCommandToolStripMenuItem_Click(object sender, EventArgs e)
        { 
            for (int i = 0; i < this.dataGridView1.SelectedRows.Count; i++)
            {
                string status = this.dataGridView1.SelectedRows[i].Cells["状态"].Value.ToString();
                if (status == "登记")
                {
                    string car_no = this.dataGridView1.SelectedRows[i].Cells["车号"].Value.ToString();
                    string login_time = this.dataGridView1.SelectedRows[i].Cells["登记时间"].Value.ToString();
                    string update_sql = "update TRUCK_CARGO_LOGIN set TRUCKSTATUS=1,COMMANDTIME=sysdate where logintime=to_date('" + login_time + "','yyyy-mm-dd hh24:mi:ss') and TRUCKNO='" + car_no + "' and TRUCKSTATUS<>'9'";
                    db.ExecuteNonQuery(db.GetSqlStringCommand(update_sql.ToString()));
                    string sID = create_id();
                    string insert_sql = "insert into TRUCK_COMMAND_LOG(ID,TRUCKNO,YHBH,XM,XGSJ,XGNR,IOYARDID,SXFC,MODULE,SEND) " +
                        "values(" + sID + ",'" + car_no + "','" + Session.YHBH + "','" + Session.XM + "',sysdate,'理货中心发送进车指令','1','0','市入车辆状态','1')";
                    db.ExecuteNonQuery(db.GetSqlStringCommand(insert_sql));
                }
            }
            load_data();
        }

        private void CancelCommandToolStripMenuItem_Click(object sender, EventArgs e)
        {
             
            for (int i = 0; i < this.dataGridView1.SelectedRows.Count; i++)
            {
                string status = this.dataGridView1.SelectedRows[i].Cells["状态"].Value.ToString();
                if (status == "发指令")
                {
                    string car_no = this.dataGridView1.SelectedRows[i].Cells["车号"].Value.ToString();
                    string login_time = this.dataGridView1.SelectedRows[i].Cells["登记时间"].Value.ToString();
                    string update_sql = "update TRUCK_CARGO_LOGIN set TRUCKSTATUS=0,COMMANDTIME=sysdate where logintime=to_date('" + login_time + "','yyyy-mm-dd hh24:mi:ss') and TRUCKNO='" + car_no + "' and TRUCKSTATUS<>'9'";
                    db.ExecuteNonQuery(db.GetSqlStringCommand(update_sql.ToString()));
                    string sID = create_id();
                    string insert_sql = "insert into TRUCK_COMMAND_LOG(ID,TRUCKNO,YHBH,XM,XGSJ,XGNR,IOYARDID,SXFC,MODULE,SEND) " +
                        "values(" + sID + ",'" + car_no + "','" + Session.YHBH + "','" + Session.XM + "',sysdate,'理货中心取消进车指令','1','0','市入车辆状态','0')";
                    db.ExecuteNonQuery(db.GetSqlStringCommand(insert_sql));
                }
            }
            load_data();
        }

        private string create_id()
        {
            string sequence = "";
            string sqlStr = "select SEQ_TRUCK.nextval from dual";
            DataSet ds1 = db.ExecuteDataSet(db.GetSqlStringCommand(sqlStr));
            if (ds1.Tables[0].Rows.Count > 0)
            {
                sequence = ds1.Tables[0].Rows[0][0].ToString();
            }
            string year_str = DateTime.Now.Year.ToString();
            string month_str = DateTime.Now.Month.ToString();
            string day_str = DateTime.Now.Day.ToString();
            string id = year_str + month_str + day_str + sequence;
            return id;
        }

        private void dataGridView1_CellEndEdit(object sender, DataGridViewCellEventArgs e)
        {
            if (e.ColumnIndex == 5)
            {
                string car_no = (string)dataGridView1.Rows[e.RowIndex].Cells["车号"].Value.ToString();
                string yard = (string)dataGridView1.Rows[e.RowIndex].Cells[e.ColumnIndex].Value.ToString();
                string update_sql = "update TRUCK_CARGO_LOGIN set YARD='" + yard + "' where  TRUCKNO='" + car_no + "'";
                db.ExecuteNonQuery(db.GetSqlStringCommand(update_sql.ToString()));
            }
            if (e.ColumnIndex == 6)
            {
                string car_no = (string)dataGridView1.Rows[e.RowIndex].Cells["车号"].Value.ToString();
                string yard = (string)dataGridView1.Rows[e.RowIndex].Cells[e.ColumnIndex].Value.ToString();
                string update_sql = "update TRUCK_CARGO_LOGIN set ZYJX='" + yard + "' where  TRUCKNO='" + car_no + "'";
                db.ExecuteNonQuery(db.GetSqlStringCommand(update_sql.ToString()));
            }
        }
    }
}
